﻿//------------------------------------------------------------------------
// 程序名称：
// 功能：封装SQL的操作
// 编制者：宋亮亮         完成日期：2010.10.20
// 版本：v1.0
// 其他：
//-----------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Specialized;
using System.Web;

namespace MFTool
{
    /// <summary>
    /// sql查询语句及返回结果的列
    /// </summary>
    public class SQLAndFields
    {
        public string SQL { get; set; }
        public List<string> Fields { get; set; }
    }
    /// <summary>
    /// SQL帮助类
    /// </summary>
    public class SqlHelper
    {
        protected string m_strSqlConnect = "";
        private static Dictionary<string, object> _dic = new Dictionary<string, object>();

        public SqlHelper()
        {

        }

        public SqlHelper(string conn)
        {
            System.Configuration.ConfigurationManager.ConnectionStrings[conn].CheckNotNull("无法找到name为{0}的ConnectionStrings".ReplaceArg(conn));
            m_strSqlConnect = System.Configuration.ConfigurationManager.ConnectionStrings[conn].ToString();
        }

        public void SetConn(string conn)
        {
            System.Configuration.ConfigurationManager.ConnectionStrings[conn].CheckNotNull("无法找到name为{0}的ConnectionStrings".ReplaceArg(conn));
            m_strSqlConnect = System.Configuration.ConfigurationManager.ConnectionStrings[conn].ToString();
        }

        public static SqlHelper CreateSqlHelper(string connName)
        {
            string key = string.Format("Conn_{0}", connName);

            SqlHelper sqlHelper = null;
            if (HttpContext.Current != null)
            {
                if (HttpContext.Current.Items[key] != null)
                    return HttpContext.Current.Items[key] as SqlHelper;

                if (System.Configuration.ConfigurationManager.ConnectionStrings[connName] == null)
                    throw new Exception(string.Format("配置文件中不包含数据库连接字符串：{0}", connName));

                sqlHelper = new SqlHelper(connName);
                HttpContext.Current.Items[key] = sqlHelper;
            }
            else
            {
                if (_dic.Keys.Contains(key))
                    return _dic[key] as SqlHelper;

                sqlHelper = new SqlHelper(connName);
                _dic[key] = sqlHelper;
            }

            return sqlHelper;
        }

        public static T CreateSqlHelper<T>(string connName) where T : SqlHelper
        {
            string key = string.Format("Conn_{0}", connName);

            T sqlHelper = null;
            if (HttpContext.Current != null)
            {
                if (HttpContext.Current.Items[key] != null)
                    return HttpContext.Current.Items[key] as T;

                if (System.Configuration.ConfigurationManager.ConnectionStrings[connName] == null)
                    throw new Exception(string.Format("配置文件中不包含数据库连接字符串：{0}", connName));

                sqlHelper = System.Activator.CreateInstance<T>();
                sqlHelper.SetConn(connName);
                HttpContext.Current.Items[key] = sqlHelper;
            }
            else
            {
                if (_dic.Keys.Contains(key))
                    return _dic[key] as T;

                sqlHelper = System.Activator.CreateInstance<T>();
                sqlHelper.SetConn(connName);
                _dic[key] = sqlHelper;
            }

            return sqlHelper;
        }        

        protected virtual void BeforeExcuteSql(ref string sql, ref QueryBulider qb)
        {

        }

        public bool Exist()
        {
            return new SqlConnection(m_strSqlConnect).State == ConnectionState.Open;
        }

        public SQLAndFields GetSQLAndFields(string sql)
        {
            SQLAndFields res = new SQLAndFields();
            res.SQL = sql;
            res.Fields = new List<string>();

            DataTable dt = ExcuteTable("select top 1 * from ({0}) tmppppppt".ReplaceArg(sql));
            foreach(DataColumn dc in dt.Columns)
            {
                res.Fields.Add(dc.ColumnName);
            }
            return res;
        }

        /// <summary>
        /// 1.0 执行查询语句，返回一个表a
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">参数数组</param>
        /// <returns>返回一张表</returns>
        public DataTable ExcuteTable(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, m_strSqlConnect); 
            DataTable dt = new DataTable();
            QueryBulider qb = null;
            BeforeExcuteSql(ref sql, ref qb);
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                Logger.GetCurMethodLog().Write(EnumLogLevel.Error, "【"+ m_strSqlConnect + "】访问数据库失败", ex);
                throw new Exception("访问数据库失败", ex);
            }
            return dt;
        }

        public DataGridData ExcuteDataGrid(string sql, QueryBulider qb,string orderBy = "")
        {
            string resSql = sql;
            int? totalCount = null;
            if (qb != null)
            {
                sql = "select newid() as _id ,_tmpTable.* from ({0}) _tmpTable".ReplaceArg(sql);
                int pageId = qb.page;
                int pageSize = qb.rows;
                int startRowNumber = (pageId - 1) * pageSize;
                int endRowNumber = pageId * pageSize;
                string pageSql = string.Format("select * from (select  ROW_NUMBER() over(order by (select 0))  as rows, son.*  from ({0}) son) tmp where rows between {1} and {2}", sql, startRowNumber, endRowNumber);
                
                var totalCountObj = ExecScalar(string.Format("select count(*) FROM ({0})tmp", sql));
                if (totalCountObj != null && totalCountObj != DBNull.Value)
                {
                    totalCount = (int)totalCountObj;
                }

                BeforeExcuteSql(ref pageSql, ref qb);
                if (!string.IsNullOrEmpty(qb.GetWhereString()))
                {
                    resSql = "select * from ({0}) tmpppppt where {1}".ReplaceArg(pageSql, qb.GetWhereString());
                }
                else
                {
                    resSql = pageSql;
                }                
            }
            else
            {
                BeforeExcuteSql(ref resSql, ref qb);
            }

            
            if(!string.IsNullOrEmpty(orderBy))
            {
                if (!orderBy.ToLower().Contains("order") && !orderBy.ToLower().Contains("by"))
                {
                    resSql += (" order by " + orderBy);
                }
                else
                {
                    resSql += (" " + orderBy);
                }
            }
            else
            {
                resSql += " order by Id desc";
            }

            SqlDataAdapter da = new SqlDataAdapter(resSql, m_strSqlConnect);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                Logger.GetCurMethodLog().Write(EnumLogLevel.Error, "【" + m_strSqlConnect + "】访问数据库失败", ex);
                throw new Exception("访问数据库失败", ex);
            }
            return new DataGridData() { rows = dt, total = totalCount ?? dt.Rows.Count };
        }

        /// <summary>
        /// 2.0 执行增删改的方法
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">参数数组</param>
        /// <returns>返回一条记录</returns>
        public int ExcuteNoQuery(string sql, params SqlParameter[] ps)
        {
            using (SqlConnection conn = new SqlConnection(m_strSqlConnect))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(ps);
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 3.0 执行存储过程的方法
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="ps">参数数组</param>
        /// <returns></returns>
        public int ExcuteProc(string procName, params SqlParameter[] ps)
        {
            using (SqlConnection conn = new SqlConnection(m_strSqlConnect))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(procName, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(ps);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Logger.GetCurMethodLog().Write(EnumLogLevel.Error, "【" + m_strSqlConnect + "】访问数据库失败", ex);
                    throw new Exception("访问数据库失败", ex);
                }                
            }
        } 
        
        /// <summary>
        /// 4.0 查询结果集，返回的是首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">参数数组</param>
        /// <returns></returns>
        public object ExecScalar(string sql, params SqlParameter[] ps)  //调用的时候才判断是什么类型
        {
            using (SqlConnection conn = new SqlConnection(m_strSqlConnect))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(ps);
                try
                {
                    return command.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Logger.GetCurMethodLog().Write(EnumLogLevel.Error, "【" + m_strSqlConnect + "】访问数据库失败", ex);
                    throw new Exception("访问数据库失败", ex);
                }                
            }
        }

        public static IEnumerable<string> GetColumnNames(string connName, string tableName)
        {
            string sql =  "select name from sys.columns where object_id=object_id('{0}')".ReplaceArg(tableName);
            var list = CreateSqlHelper(connName).ExcuteTable(sql).AsEnumerable();
            return list.Select(a => a["name"].ToString());
        }

        /// <summary>
        /// Name, TypeName
        /// </summary>
        /// <param name="connName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static IEnumerable<Dictionary<string,object>> GetColumnNameWithType(string connName,string tableName)
        {
            string sql = @"select A.Name,
case when B.Name = 'nvarchar' or B.Name = 'varchar'  then B.Name + '(' + 
case when cast(COLUMNPROPERTY(A.id,A.name,'PRECISION') as varchar(50)) = '-1' then 'max'
else cast(COLUMNPROPERTY(A.id,A.name,'PRECISION') as varchar(50)) end
 + ')'
when B.Name = 'decimal' then B.Name  + '(' + cast(COLUMNPROPERTY(A.id,A.name,'PRECISION') as varchar(50))
 +','+ cast(isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0) as varchar(50)) + ')'
else B.Name end TypeName
from syscolumns A  LEFT JOIN SYSTYPES B ON A.xtype =B.XUSERTYPE
inner join sysobjects D on A.id=D.id and D.xtype='U' and D.name<>'dtproperties' 
where D.name = '{0}'".ReplaceArg(tableName);
            DataTable dt = CreateSqlHelper(connName).ExcuteTable(sql);
            return dt.ToDicList();
        }

        /// <summary>
        /// "bit", "char(10)", "date", "datetime","decimal(18, 0)","float",
        /// "image","int","ntext",
        /// "numeric(18, 0)","nvarchar(50)","nvarchar(MAX)","text","uniqueidentifier","varchar(50)",
        /// "varchar(MAX)"
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<string> SQlCommonServerTypeList()
        {
            return new List<string> { 
                "bit", "char(10)", "date", "datetime","decimal(18, 0)","float",
                "image","int","ntext",
                "numeric(18, 0)","nvarchar(50)","nvarchar(MAX)","text","uniqueidentifier","varchar(50)",
                "varchar(MAX)"
            };
        }
    }
}